OpenEdge Development:<br />ProDataSets Query OFF-END Event

There has long been an OFF-END GUI event for the Progress browse control. Developers can use this to detect the end of the available data in the query the browse is displaying. For example, you could then retrieve additional batches of data and append them to the rows in the table and re-open the query so that they are added to the browse. The ProDataSet supports an OFF-END event for its temp-table queries. It takes care of this function for you, so that you do not need to code an OFF-END browse trigger block to handle this, or even depend on there being a browse at all. In addition, Progress queries have a QUERY-OFF-END condition you can use to detect the end of the query’s data when you are navigating through the data programmatically. The ProDataSet event can respond to this case as well, when there is no browse to trigger a GUI event. Regardless of how the end-of-data condition is detected, the query itself can respond to running out of rows so that an event handler can react appropriately, whether it is to retrieve more data from the server or take other action.

The OFF-END event is available for any query on a ProDataSet temp-table. The OFF-END event occurs when the query is positioned beyond the last row in the query, no matter how this is done. This can be because of a browse scroll to the end of the query, or a GET-NEXT() method or GET NEXT statement on the query beyond the last row.

This event can be attached to the query handle using the same SET-CALLBACK-PROCEDURE method the FILL events use, as shown in the following context:

Syntax
`query-handle`:`SET-CALLBACK-PROCEDURE`(“`OFF-END`”, `event-procedure` [, `procedure-handle` ] ).

query-handle is the handle of a static or dynamic query.

event-procedure is the name of an internal procedure to run that handles the event.

procedure-handle is a running procedure instance containing that event-procedure. As for other events, the default is THIS-PROCEDURE.

In keeping with the calling sequence for the FILL and temp-table change events, the ProDataSet is passed in as an INPUT parameter implicitly BY-REFERENCE, providing the event procedure full access to all the data at no cost (because there is no copying of data involved). And as with other callback events, your code can use the APPLY-CALLBACK method to invoke the event handler programmatically.

A typical use of these events would be to fetch additional batches of data from the server if not all data has been retrieved and sent to the client. The event handler for OFF-END can find the last currently available row and pass its key to the server as a starting point for the next batch. There’s an example of using this technique to provide data batching later in Chapter 8, " Batching Data with ProDataSets." The event procedure can of course also look at other information in the ProDataSet, including the current row in other tables (so that the query requesting more data could identify the parent for example), and whatever else is helpful.

Note that this event is similar to the existing QUERY-OFF-END query attribute. The difference is that the QUERY-OFF-END attribute is a condition that must be tested at a specific place in the application code, whereas the callback event procedure executes whenever the condition occurs regardless of where it happens in the application code or the user interface. This allows a single event handler to be executed whenever the condition occurs.

The event can be attached only to a query that is on a single buffer for a ProDataSet temp-table; it is not possible to attach the events to a query that involves a join. Because ProDataSet temp-tables can already mask database table joins, this should not normally be a serious restriction.

The query must be defined as SCROLLING (so that it has an internal result-list maintained by Progress).

The query open statement or method should not have the INDEXED-REPOSITION keyword; if present, it is ignored.

The SET-CALLBACK-PROCEDURE method must come before the query is opened for the first time to assure that the event is triggered properly. Once the callback is registered, you can open and close the query as much as you need to and the callback procedure remains attached to the OFF-END event.

If the event handler is able to add a row or rows to the end of the temp-table, it must RETURN NO-APPLY in order to prevent the QUERY-OFF-END (or browse OFF-END) condition from occurring. If the event handler returns NO-APPLY, the application event or code that triggered the event never even knows that the attempt to keep scrolling through the query initially failed.

If the event handler is unable to add more rows to the temp-table, it should not RETURN NO-APPLY. That would result in an infinite loop, since the NO-APPLY will prevent the OFF-END condition from happening when it should.

If you execute a GET LAST statement or GET-LAST() method on the query, the event handler is called repeatedly until it does not return NO-APPLY, signifying that all records have been retrieved. In the case of a very large set of rows this can result in a significant wait while all rows are retrieved. If you need to take advantage of the behavior that INDEXED-REPOSITION provides for you, allowing you to rebuild the query’s result-list from the end, for example, so that you can jump directly to the last row, then you cannot use an OFF-END event handler to accomplish this.

The handler will get an error if it does an OPEN, GET, or REPOSITION on the query itself, since the application is still in the middle of a query operation like NEXT or PREV that is suspended while the event handler executes. Also, any references to a different row in the query’s temp-table must be done using a separate buffer.

References to the Progress SELF keyword in the event handler evaluate to the query handle. From this the code can access the query’s buffer if needed, using the construct SELF:GET-BUFFER-HANDLE.

The query open can have the PRESELECT keyword or a BY clause for sorting, but note that in the case of a PRESELECT or non-indexed sort, the handler will be called repeatedly until all records are read before starting the post-select loop. This is not useful when the event handler is used for batching, but it is supported primarily so that dynamic cases do not have to check for special restrictions on the query.

The OFF-END event fires before the QUERY-OFF-END condition is set, and before any other condition that signals the end of available data. This means, for example, that if there is an OFF-END event on a query, and the end of the rows in the query is reached because of some action (such as GET-NEXT), then the OFF-END event fires. If that event results in more rows being added to the query, then the QUERY-OFF-END condition does not occur. In this way, a single DO WHILE NOT hQuery:QUERY-OFF-END block, or the act of scrolling down through a browse, can continue seamlessly until the OFF-END fails to add any new rows to the query.

The "Buffer BATCH-SIZE and LAST-BATCH attributes" section shows how to use the OFF-END event to providing batching for an application window when you need to be able to scroll seamlessly through a large number of rows that cannot be retrieved all at once.